const db = require('../dbbase/db');
const getData = require("../util/data");
const sql = require('mssql')

//获取每天指标信息
exports.getTargetDayInfo = (req, res, next) => {
    const startDate = getData.getCurrentMonthFirst();
    const endDate = getData.getCurrentMonthLast();
    console.log(startDate);
    console.log(endDate);
    const sql = "select to_char(t.BTIME, 'YYYY-MM-DD') 日期,COUNT(*) 产量,COUNT(PDID) 缺陷量,COUNT(1)-COUNT(PDID) 有效产出  from mwdk_pa_work_library t "
        + "WHERE t.btime between TO_DATE('" + startDate + "','yyyy-mm-dd HH24:mi:ss') and TO_DATE('" + endDate + "','yyyy-mm-dd HH24:mi:ss') "
        + "and MATE_TYPE not like '%去毛刺%' "
        + "group by to_char(t.BTIME, 'YYYY-MM-DD') "
        + "ORDER BY 日期 desc "
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取本月总指标
exports.getTargetMouthInfo = (req, res, next) => {
    const startDate = getData.getCurrentMonthFirst();
    const endDate = getData.getCurrentMonthLast();
    const sql = "select COUNT(*) totle,COUNT(PDID) quelity,COUNT(1)-COUNT(PDID) outTotle  from mwdk_pa_work_library t "
        + "WHERE t.btime between TO_DATE('" + startDate + "','yyyy-mm-dd HH24:mi:ss') and TO_DATE('" + endDate + "','yyyy-mm-dd HH24:mi:ss') "
        + "and MATE_TYPE not like '%去毛刺%' "
    const params = [];
    db.query(sql, params).then((result) => {
        res.status(200).json(result)
    }).catch((err) => {
        console.log(err);
    });
}
//获取入库数 SQLSERVER AIS20120302012007 
exports.getInputCount = (req, res, next) => {
    const startDate = getData.getCurrentMonthFirst();
    const endDate = getData.getCurrentMonthLast();
    var config = {
        user: 'sa',  
        password: 'mov123!',   
        server: 'SQLSERVER', 
        database: 'AIS20120302012007',   
        encrypt: false   //  不加上这个会报错：connect error
    };

    sql.connect(config, function (err) {
        if (err) {
            throw err;
        } else {
            // create Request object
            var request = new sql.Request();
            // query to the database and get the records
            var sqlStr = "SELECT SUM( b.FQtypass ) count "
            + "FROM SHProcRptMain a,  SHProcRpt b "
            + "WHERE a.FDate between '" + startDate +"' and '" + endDate +"' "
            + "AND a.FOperID= 40037  AND a.FInterID= b.FinterID  "
            request.query(sqlStr, function (err, recordset) {
                if (err) {
                    throw err;
                } else {
                    res.status(200).json(recordset)
                }
            });
        }
    });
}

